﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.EntityClient;
using System.Data.Objects;
using System.IO;
using System.Linq;
using System.Transactions;
using System.Web.Mvc;
using System.Web.Script.Serialization;
using MeresModeling;
using Newtonsoft.Json;
using Orchard.Themes;
using Eking.Framework;

namespace Meres.Controllers
{
    [Themed]
    public class AdministrationController : Controller
    {
        public PartialViewResult Index()
        {
            return PartialView("AdminView");
        }

        public ActionResult AdminFunctionView(int funcId)
        {
            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                if (funcId == 0)
                {
                    return PartialView("AdminView.PersonCategory", MeresMainController.Model.PersonCategories.ToList());
                }

                if (funcId == 1)
                {
                    return PartialView("AdminView.MembershipGroup", MeresMainController.Model.MembershipGroups.ToList());
                }

                if (funcId == 7)
                {
                    var dir = GetJsonConfigDir();
                    var skip = new List<string> { "ConfigHelper.js" };

                    var query = from f in Directory.EnumerateFiles(dir, "*.js").Select(Path.GetFileName)
                                where !skip.Contains(f)
                                orderby f
                                select f;

                    return PartialView("AdminView.JsonEditor", query);
                }

                if (funcId == 6)
                {
                    return GetDbMasterView();
                }

                return PartialView("SimpleView", funcId);
            }
        }

        private JsonResult GetDbMasterView()
        {
            var db = MeresMainController.Model;
            var data = db.GetTableModels();
            var html = this.RenderPartialViewToString("AdminView.DbMaster", data);
            return Json(new
            {
                Html = html,
                ViewModel = data
            }, JsonRequestBehavior.AllowGet);
        }

        public string LoadJsonFile(string fileName)
        {
            return System.IO.File.ReadAllText(Path.Combine(GetJsonConfigDir(), fileName));
        }

        public void SaveJsonFile(string fileName, string content)
        {
            var dir = GetJsonConfigDir();
            var buDir = Path.Combine(dir, "__bak");
            if (!Directory.Exists(buDir))
                Directory.CreateDirectory(buDir);
            var file = Path.Combine(dir, fileName);
            if (System.IO.File.Exists(file))
                System.IO.File.Copy(file, Path.Combine(buDir, fileName + "." + DateTime.Now.ToString("yyyy_MM_dd-hh_mm_ss") + ".bak"));
            System.IO.File.WriteAllText(file, content);
        }

        private string GetJsonConfigDir()
        {
            return Path.Combine(MeresMainController.ModuleDir, "Scripts", "Configuration");
        }


        public JsonResult TableDatajqGridEdit(string tableModel, string oper, string id)
        {
            var table = (new JavaScriptSerializer()).Deserialize<DbTableModel>(tableModel);

            var vals = new Dictionary<DbTableColumnModel, object>();
            foreach (var col in table.Columns)
            {
                vals[col] = Request.Form.Get(col.Name);
            }

            if (oper == "edit")
            {
                UpdateTableData(table, vals);
            }

            if (oper == "del")
            {
                DeleteTableData(table, vals);
            }

            if (oper == "add")
            {
                var val = InsertTableData(table, vals);
                return Json(val, JsonRequestBehavior.AllowGet);
            }

            return null;
        }



        public JsonResult TableDatajqGridQuery(string sidx, string sord, int page, int rows,
                bool _search, string searchField, string searchOper, string searchString, string tableModel)
        {
            var js = new JavaScriptSerializer();
            var table = js.Deserialize<DbTableModel>(tableModel);

            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                var data = GetAllData(table);

                var rowData = new List<object>();

                foreach (DataRow row in data.Rows)
                {
                    rowData.Add(new
                        {
                            id = BuilId(row, table),
                            cell = row.ItemArray
                        });
                }

                var jsonData = new
                {
                    total = 1, // total page
                    page = 0,
                    records = data.Rows.Count, // total item
                    rows = rowData
                };

                return Json(jsonData, JsonRequestBehavior.AllowGet);

            }
        }

        private static string BuilId(DataRow row, DbTableModel tableModel)
        {
            return tableModel.Columns.Where(t => t.IsPrimaryKey).Aggregate("", (current, columnModel) => current + "-" + row[columnModel.Name]).Trim('-');
        }

        private static DataTable GetAllData(DbTableModel table)
        {
            //var output = new List<object[]>();

            var dt = new DataTable(table.Name);
            table.Columns.ForEach(c => dt.Columns.Add(new DataColumn(c.Name, typeof(object))));

            var db = MeresMainController.Model;
            var conn = ((EntityConnection)db.Connection).StoreConnection;
            conn.Open();
            var cmd = conn.CreateCommand();
            var cols = table.Columns.Aggregate("", (a, b) => a + ",me.[" + b.Name + "]").TrimStart(',');

            cmd.CommandText = string.Format("SELECT {0} FROM {1} as me", cols, table.Name);
            using (var rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
            {
                while (rdr.Read())
                {
                    var row = new object[table.Columns.Count];
                    for (var i = 0; i < row.Length; i++)
                    {
                        row[i] = rdr.GetValue(i);
                    }

                    dt.Rows.Add(row);
                }
            }
            conn.Close();

            return dt;
        }

        private static void DeleteTableData(DbTableModel table, Dictionary<DbTableColumnModel, object> data)
        {
            var db = MeresMainController.Model;
            
            var conn = ((EntityConnection)db.Connection).StoreConnection;
            conn.Open();
            var cmd = conn.CreateCommand();

            const string sql = "DELETE FROM {0} WHERE {1}";
            var tName = table.Name;
            var primaries = table.Columns.Where(t => t.IsPrimaryKey).ToList();
            var conditions = primaries.Aggregate("--", (a, b) => a + " AND " + string.Format("[{0}]=@{0}", b.Name)).Replace("-- AND ", "");

            if (primaries.Count == 0)
                throw new Exception("Don't support non-primary key table");

            foreach (var o in primaries)
            {
                var para = cmd.CreateParameter();
                para.ParameterName = "@" + o.Name;
                para.Value = data[o];

                cmd.Parameters.Add(para);
            }

            cmd.CommandText = string.Format(sql, tName, conditions);

            cmd.ExecuteNonQuery();

            conn.Close();
        }

        public static object InsertTableData(DbTableModel table, Dictionary<DbTableColumnModel, object> data)
        {
            var db = MeresMainController.Model;
            var conn = ((EntityConnection)db.Connection).StoreConnection;
            conn.Open();
            var cmd = conn.CreateCommand();

            const string sql = "INSERT INTO {0}({1}) VALUES({2});";
            var tName = table.Name;
            var vals = "";
            var cols = "";
            var primaries = table.Columns.Where(t => t.IsPrimaryKey).ToList();
            var primaryCount = primaries.Count;

            if (primaries.Count == 0)
                throw new Exception("Don't support non-primary key table");

            foreach (var o in data.Keys)
            {
                var para = cmd.CreateParameter();
                para.ParameterName = "@" + o.Name;
                para.Value = data[o];

                cmd.Parameters.Add(para);

                if (primaryCount == 1 && o.IsPrimaryKey)
                    continue;

                cols += string.Format("[{0}],", o.Name);
                vals += string.Format("@{0},", o.Name);
            }
            cols = cols.Trim(',');
            vals = vals.Trim(',');

            cmd.CommandText = string.Format(sql, tName, cols, vals);

            cmd.ExecuteReader();

            conn.Close();

            return null;
        }

        private static void UpdateTableData(DbTableModel table, Dictionary<DbTableColumnModel, object> data)
        {
            var db = MeresMainController.Model;
            var conn = ((EntityConnection)db.Connection).StoreConnection;
            conn.Open();
            var cmd = conn.CreateCommand();

            const string sql = "UPDATE {0} SET {1} WHERE {2}";
            var tName = table.Name;
            var set = "";
            var primaries = table.Columns.Where(t => t.IsPrimaryKey).ToList();
            var conditions = primaries.Aggregate("--", (a, b) => a + " AND " + string.Format("[{0}]=@{0}", b.Name)).Replace("-- AND ", "");
            var primaryCount = primaries.Count;

            if (primaries.Count == 0)
                throw new Exception("Don't support non-primary key table");

            foreach (var o in data.Keys)
            {
                var para = cmd.CreateParameter();
                para.ParameterName = "@" + o.Name;
                para.Value = data[o];

                cmd.Parameters.Add(para);

                if (primaryCount == 1 && o.IsPrimaryKey)
                    continue;

                set += string.Format("[{0}] = @{0},", o.Name);
            }

            set = set.Trim(',');

            cmd.CommandText = string.Format(sql, tName, set, conditions);

            cmd.ExecuteNonQuery();

            conn.Close();
        }

        public void TestPerceiveData(int id, DbTableModel tableModel)
        {

        }
    }
}
